/****** Object:  StoredProcedure [dbo].[IsUserRegistered]    Script Date: 04/25/2009 11:24:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IsUserRegistered] 
	@UserAlias varchar(50),
	@IsRegistered BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @id bit
	DECLARE	@ProcedureName sysname
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
	
	BEGIN TRY	
		SELECT @id = UserId from PortalUsers where UserAlias = @UserAlias
		
		IF @id IS NULL
			SET @IsRegistered = 0
		ELSE
			SET @IsRegistered = 1
						
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	GOTO ExitProc

ErrorHandler:

ExitProc:
	RETURN @IsRegistered
			
END
GO
/****** Object:  StoredProcedure [dbo].[GetWidgets]    Script Date: 04/25/2009 11:24:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetWidgets] 
	@retVal int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @UserId INT
	DECLARE	@ProcedureName sysname

	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)
	SET @retVal = 0

	BEGIN TRANSACTION
	BEGIN TRY

	SELECT w.* FROM Widget w Where w.Active=1
	
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	COMMIT TRANSACTION

	IF @@ERROR = 0 
		SET @retVal = 1
	ELSE
		SET @retVal = 0	

	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

	RETURN @retVal

ExitProc:
	RETURN @retVal
END
GO

/****** Object:  StoredProcedure [dbo].[GetUserId]    Script Date: 04/25/2009 11:24:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserId] 
	@UserAlias varchar(50),
	@UserId int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @id int
	DECLARE	@ProcedureName sysname
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
	
	BEGIN TRANSACTION
	BEGIN TRY	
		SELECT @id = UserId from PortalUsers where UserAlias = @UserAlias
		
		iF @id IS NULL
		BEGIN
			--Create a new user.
			INSERT INTO PortalUsers(UserName, UserAlias, LastActivityDate) values (@UserAlias, @UserAlias, GETDATE())
			SET @id = @@IDENTITY 
		END			
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	COMMIT TRANSACTION

	SET @UserId = @id
	
	IF @UserId IS NULL
		SET @UserId = -1

	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

	RETURN 1

ExitProc:
	RETURN 0
			
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteWidget]    Script Date: 04/25/2009 11:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteWidget]
	@UserAlias varchar(50),
	@WidgetId int,
	@RetVal BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @UserId INT	
	DECLARE @id bit
	DECLARE	@ProcedureName sysname
	--DECLARE @UpdateString varchar(4000)
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
			
	SELECT @UserId = UserId from PortalUsers where UserAlias = @UserAlias
	IF @UserId IS NULL
		RETURN;
					
	IF ((@WidgetId IS NULL) OR (@WidgetId <= 0))
		RETURN;

	--See if there we have widget to remove
	IF NOT EXISTS ( SELECT WidgetInstanceId FROM WidgetInstance WHERE ((WidgetId = @WidgetId) AND (UserId = @UserId)))  	
		RETURN;
		
	BEGIN TRANSACTION		
	BEGIN TRY

		DELETE WidgetInstance 
		WHERE 
		WidgetId = @WidgetId AND UserId = @UserId
		
		IF ((@@ERROR = 0) AND (@@ROWCOUNT = 1))
			SET @RetVal = 0 --Success
		ELSE
			SET @RetVal = 1
						
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	COMMIT TRANSACTION 
	
	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

ExitProc:
	RETURN @RetVal
			
END
GO
/****** Object:  StoredProcedure [dbo].[CreateDefaultWidgets]    Script Date: 04/25/2009 11:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CreateDefaultWidgets] 
	@UserAlias varchar(50),
	@retVal int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @UserId INT
	DECLARE @DefaultWidgetCount INT
	--DECLARE @WidgetsPerColumn INT
	DECLARE @WidgetsPerRow INT
	DECLARE @I INT, @ROW INT, @COL INT 
	DECLARE	@ProcedureName sysname
	DECLARE @UserWidgetCount INT
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)
	SET @retVal = 0

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
	
	SELECT @UserId = UserId from PortalUsers where UserAlias = @UserAlias
	IF @UserId IS NULL
		RETURN;

	BEGIN TRANSACTION
	BEGIN TRY
	
		--First step: Remove the widgets which are not part of default set.
		DELETE WidgetInstance
		WHERE UserId = @UserId AND WidgetId IN (SELECT WidgetId FROM Widget WHERE IsDefault = 0)
			
		--Second step: Insert new and update existing...	
		--Get Default Widget count.
		SELECT @DefaultWidgetCount = COUNT(W.WidgetId) FROM Widget W WHERE W.IsDefault = 1 and Active = 1
		--SET @WidgetsPerColumn = CAST(CEILING( CAST(@DefaultWidgetCount AS FLOAT)/3.0) AS INT)
		SET @WidgetsPerRow = 3.0

		SET @I = 0
		SET @ROW = 0
		SET @COL = 0
		
		WHILE @I < @DefaultWidgetCount
		BEGIN
			DECLARE @WidgetId INT, @VersionNo INT
			DECLARE @Title NVARCHAR(255), @DefaultState NVARCHAR(MAX)
			
			SELECT @WidgetId = W.WidgetId, @Title = W.Name, @DefaultState = W.DefaultState, @VersionNo = W.VersionNo FROM Widget W WHERE W.OrderNo = ( @I + 1) and Active = 1
			
			--Check to make sure this is not a duplicate insert.
			IF NOT EXISTS ( SELECT Wi1.WidgetInstanceId FROM WidgetInstance Wi1 WHERE ((Wi1.UserId = @UserId) AND (Wi1.WidgetId = @WidgetId)))
			BEGIN
				INSERT INTO WidgetInstance
				( WidgetId, UserId, ColumnNo, OrderNo, Expanded, Title, [State], CreatedDate, LastUpdate, VersionNo)
				VALUES
				( @WidgetId, @UserId, @COL, @ROW, 1, @Title, @DefaultState, getdate(), getdate(), @VersionNo)
			END
			ELSE
			BEGIN --Update, reset.
				UPDATE WidgetInstance
				SET ColumnNo = @COL
				, OrderNo = @ROW
				, Expanded = 1
				, Title = @Title
				,[State] = @DefaultState
				, LastUpdate = getdate()
				, VersionNo = @VersionNo
				WHERE ((UserId = @UserId) AND (WidgetId = @WidgetId))		
			END
			
			/*SET @ROW = @ROW + 1
			IF @ROW >= @WidgetsPerColumn
			BEGIN
				SET @ROW = 0
				SET @COL = @COL + 1
			END*/

			SET @COL = @COL + 1
			IF @COL >= @WidgetsPerRow
			BEGIN
				SET @COL = 0
				SET @ROW = @ROW + 1
			END
			
			SET @I = @I + 1		
		END
	
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	COMMIT TRANSACTION

	IF @@ERROR = 0 
		SET @retVal = 1
	ELSE
		SET @retVal = 0	

	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

	RETURN @retVal

ExitProc:
	RETURN @retVal
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateWidget]    Script Date: 04/25/2009 11:24:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateWidget] 
	@UserAlias varchar(50),
	@WidgetId int,
	@ColumnNo smallint = NULL,
	@OrderNo smallint = NULL,
	@Expanded tinyint = NULL,
	@Title nvarchar(255) = NULL,
	@State nvarchar(max) = NULL,
	@VersionNo smallint = NULL,
	@RetVal BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @id bit
	DECLARE @UserId INT		
	DECLARE	@ProcedureName sysname
	--DECLARE @UpdateString varchar(4000)
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
			
	SELECT @UserId = UserId from PortalUsers where UserAlias = @UserAlias
	IF @UserId IS NULL
		RETURN;
			
	IF ((@WidgetId IS NULL) OR (@WidgetId <= 0))
		RETURN;

	--See if there we have params to update
	IF ( (@ColumnNo IS NULL) AND (@OrderNo IS NULL) AND (@Expanded IS NULL) AND (@Title IS NULL) AND (@State IS NULL) AND (@VersionNo IS NULL))
		RETURN;
		
	BEGIN TRANSACTION		
	BEGIN TRY

		UPDATE WidgetInstance 
		SET
		ColumnNo = ISNULL(@ColumnNo, ColumnNo)
		,OrderNo = ISNULL(@OrderNo, OrderNo)
		,Expanded = ISNULL(@Expanded, Expanded)
		,Title = ISNULL(@Title, Title)
		,[State] = ISNULL(@State, [State])
		,LastUpdate = GETDATE()
		,VersionNo = ISNULL(@VersionNo, VersionNo)					
		WHERE 
		WidgetId = @WidgetId AND UserId = @UserId
		
		IF ((@@ERROR = 0) AND (@@ROWCOUNT = 1))
			SET @RetVal = 0 --Success
		ELSE
			SET @RetVal = 1
						
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH
	
	COMMIT TRANSACTION
	
	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

ExitProc:
	RETURN @RetVal
			
END
GO
/****** Object:  StoredProcedure [dbo].[AddWidget]    Script Date: 04/25/2009 11:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddWidget] 
	@UserAlias varchar(50),
	@WidgetId int,
	@ColumnNo smallint = NULL,
	@OrderNo smallint = NULL,
	@Expanded tinyint = NULL,
	@Title nvarchar(255) = NULL,
	@State nvarchar(max) = NULL,
	@VersionNo smallint = NULL,
	@RetVal BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @id bit
	DECLARE @UserId INT		
	DECLARE	@ProcedureName sysname
	--DECLARE @UpdateString varchar(4000)
	
	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
			
	SELECT @UserId = UserId from PortalUsers where UserAlias = @UserAlias
	IF @UserId IS NULL
		RETURN;
			
	IF ((@WidgetId IS NULL) OR (@WidgetId <= 0))
		RETURN;

	IF EXISTS ( SELECT WidgetInstanceId FROM WidgetInstance WHERE ((WidgetId = @WidgetId) AND (UserId = @UserId)))
		RETURN;
		
	--See if there we have params to ADD
	IF ( (@ColumnNo IS NULL) AND (@OrderNo IS NULL) AND (@Expanded IS NULL) AND (@Title IS NULL) AND (@State IS NULL) AND (@VersionNo IS NULL))
		RETURN;
		
	BEGIN TRANSACTION		
	BEGIN TRY

		INSERT INTO WidgetInstance 
		( WidgetId, UserId, ColumnNo, OrderNo, Expanded, Title, [State], CreatedDate, LastUpdate, VersionNo )
		VALUES( @WidgetId, @UserId, @ColumnNo, @OrderNo, @Expanded, @Title, @State, GETDATE(), GETDATE(), @VersionNo )  
		
		IF ((@@ERROR = 0) AND (@@ROWCOUNT = 1))
			SET @RetVal = 0 --Success
		ELSE
			SET @RetVal = 1
						
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH
	
	COMMIT TRANSACTION
	
	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

ExitProc:
	RETURN @RetVal
			
END
GO
/****** Object:  StoredProcedure [dbo].[GetWidgetInstances]    Script Date: 04/25/2009 11:24:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetWidgetInstances] 
	@UserAlias varchar(50),
	@retVal int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @UserId INT
	DECLARE	@ProcedureName sysname

	-- Basic Init
	SET @ProcedureName = OBJECT_NAME(@@PROCID)
	SET @retVal = 0

	-- Input Param Validation
	IF ((@UserAlias IS NULL) OR (@UserAlias = ''))
		RETURN;
	
	SELECT @UserId = UserId from PortalUsers where UserAlias = @UserAlias
	IF @UserId IS NULL
		RETURN;

	BEGIN TRANSACTION
	BEGIN TRY

	SELECT WI.*, W.Url FROM WidgetInstance WI INNER JOIN Widget W 
	ON WI.WidgetId = W.WidgetId 
	WHERE WI.UserId = @UserId
		and W.Active = 1
	
	END TRY
	BEGIN CATCH
		PRINT 'Error :: ' + @ProcedureName + ' - Message :: ' + ERROR_MESSAGE()
		GOTO ErrorHandler
	END CATCH

	COMMIT TRANSACTION

	IF @@ERROR = 0 
		SET @retVal = 1
	ELSE
		SET @retVal = 0	

	GOTO ExitProc

ErrorHandler:
	IF( @@TRANCOUNT > 0 )
	BEGIN
		ROLLBACK TRANSACTION
	END

	RETURN @retVal

ExitProc:
	RETURN @retVal
END
GO
